using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Excel;
using System.Diagnostics;

namespace QUANLY_POS
{
    public class ExcelLibrary
    {
        public void ExportToExcel(System.Data.DataTable ds, string strExcelFileName)
        {
            if (ds.Rows.Count == 0 || strExcelFileName == "")
            {
                return;
            }
            else
            {
                DoExport(ds, strExcelFileName);
            }

        }

        public void ExportToExcel(System.Data.DataTable ds)
        {
            System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();
            //if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            //{
                DoExport(ds, saveFileDlg.FileName);
            //}

        }

        //Export to file Excel
        private void DoExport(System.Data.DataTable ds, string strExcelFileName)
        {
            Excel.Application excel = new Excel.Application();
            int rowIndex = 1;
            int colIndex = 0;
            excel.Application.Workbooks.Add(true);
            System.Data.DataTable table = ds;
            try
            {
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }

                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
                excel.Visible = false;
                /////////////////////////////////////////////+ ".XLSX"
                excel.Save(DateTime.Now.ToString("ddMMyyyy") + ".xlsx");
                //excel.ActiveWorkbook.SaveAs(strExcelFileName + ".xlsx" , Excel.XlFileFormat.xlCurrentPlatformText, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null);
                CloseExcel(excel);

            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                KillExcelProcess(excel);
            }
        }

        public static void CloseExcel(Excel.Application Objectxl)
        {
            if (Objectxl != null)
                Objectxl.Application.Quit();
        }

        public static void KillExcelProcess(Excel.Application Objectxl)
        {
            Process[] excelProcesses = Process.GetProcessesByName("Excel");
            foreach (Process p in excelProcesses)
            {
                p.Kill();
            }
            GC.Collect();
            GC.WaitForPendingFinalizers();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Objectxl.Workbooks);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Objectxl.Application);
        }
    }
}
